package day1217;


import day1216.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class ExamresultDaoImpl implements ExamresultDao {
    @Override
    public List<Examresult> all() {
        List<Examresult>datas=new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement("SELECT * FROM s_examresult");
            rs=ps.executeQuery();
            while (rs.next()){
                Examresult ex=new Examresult();
                ex.setId(rs.getInt("id"));
                ex.setExam_date(rs.getDate("exam_date"));
                ex.setScore(rs.getDouble("score"));
                ex.setSt_id(rs.getInt("st_id"));
                ex.setSb_id(rs.getInt("sb_id"));
                datas.add(ex);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }

        return datas;
    }

    @Override
    public Integer add(Examresult ex) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="INSERT INTO `s_examresult` (`id`, `exam_date`, `score`, `st_id`, `sb_id`) VALUES (null ,?, ?, ?, ?);";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setObject(1,ex.getExam_date());
            ps.setDouble(2,ex.getScore());
            ps.setLong(3,ex.getSt_id());
            ps.setLong(4,ex.getSb_id());
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }

    @Override
    public Integer del(int id) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="DELETE FROM s_examresult WHERE id=?;";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setLong(1,id);
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }

    @Override
    public Integer update(Examresult ex) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="UPDATE `s_examresult` SET `exam_date` = ?, `score` = ?, `st_id` = ?, `sb_id` = ? WHERE `id` = ?;";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setObject(1,ex.getExam_date());
            ps.setDouble(2,ex.getScore());
            ps.setLong(3,ex.getSt_id());
            ps.setLong(4,ex.getSb_id());
            ps.setInt(5,ex.getId());
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }
}
